package raceReservationServer.dao;

import java.io.UnsupportedEncodingException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;


public class RaceServerDAO implements IRaceServerDAO{
	private java.sql.Connection con;
	public void connect(){
		try{
            Class.forName("org.sqlite.JDBC");
            con = DriverManager.getConnection("jdbc:sqlite:db/rmi-db/reservations.db");
            System.out.println("Connected to the reservations database.\n");
		}    
		catch (SQLException ex){
            System.out.println("Error connecting to the database." + ex.getMessage()); 
		}catch (ClassNotFoundException e){
			System.out.println("Error connecting to the database." + e.getMessage()); 
		}
	}
	public int getPlacesCount(String raceServer, String raceName) throws SQLException{
		ResultSet result;
		int i= 0;
        try {
        	
        	Statement stmt = con.createStatement();
            String query= "SELECT * FROM reservations WHERE `race-server`='" + raceServer + "' AND `race-name`='" + raceName + "'";
            result = stmt.executeQuery(query);
            while(result.next()){
				i++;
			}
        } catch (SQLException e) {
                System.out.println("Query error");       
        }
        return i;
	}
	public void reservePlace(String runnerName, String raceServer, String raceName) throws SQLException {
		try {
			PreparedStatement stmt = con.prepareStatement("INSERT INTO reservations VALUES (?,?,?,?)");
			stmt.setString(1, runnerName);
			stmt.setString(2, raceServer);
			stmt.setString(3, raceName);
			java.sql.Date fechDate = new java.sql.Date(new java.util.Date().getTime());
			Timestamp time= new Timestamp(new java.util.Date().getTime());

			String t= fechDate.toString(); 
			stmt.setString(4, t);
			
			stmt.executeUpdate();
		} catch (SQLException e) {
			throw e;
		}
	}
	public void disconnect(){
		try {
			con.close();
		} catch (SQLException e) {
			System.out.println("Error disconnecting from the database." + e.getMessage());
		}
	}
	public RaceServerDAO(){
		this.connect();
	}
	public static void main (String[] args ){
		RaceServerDAO r= new RaceServerDAO();

		int aux= 0; 
		try {
			r.reservePlace("Pedro Piqueras Núñez", "GipuzkoaRaces", "Behobia-San Sebastián");
			aux= r.getPlacesCount("GipuzkoaRaces", "Behobia-San Sebastián");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(aux);
	}
}
